# -*- coding: utf-8 -*-

import sys
import csv
import sqlite3
from operator import itemgetter
import SimpleHTTPServer
import SocketServer
import webbrowser

dataList = []
'''
Create a data base with a single file CSV
@param fileName the name of CSV file
'''
def dataBase(fileName):
   #open csv to read      
   spamReader = csv.reader(open('reb{0}.csv'.format(str(fileName)),\
                           'rb'),delimiter=',' )
   #connect to database
   connect = sqlite3.connect("lp_master.db")
   c = connect.cursor()
   if fileName == 0:
      #if table already exist in database it'll be deleted
      c.execute('''drop table if exists teachers ''')
      #if table doesn't exists it'll be created
      c.execute('''create table teachers (Codigo_do_Docente
                  integer, Nome text, Ultimo_Grau text, Curso text,
                  Categoria text, Regime_de_Tempo text, Ano_Lectivo text,
                  Tipo_de_Estabelecimento text, Nome_do_Estabelecimento
                  text)''')
      pass
   spamReader.next()
   #each row's value inserted into table
   for row in spamReader:
      c.execute('''insert into teachers values ("{0}", "{1}", "{2}",
                  "{3}", "{4}", "{5}", "{6}", "{7}",
                  "{8}")'''.format(row[0].replace('"', '\''),\
                  row[1].replace('"', '\''),row[2].replace('"','\''),\
                  row[3].replace('"', '\''),row[4].replace('"','\''),\
                  row[5].replace('"', '\''),row[6].replace('"', '\''),\
                  row[7].replace('"', '\''),row[8].replace('"', '\'')))
      pass
   #commit transaction
   connect.commit()
   pass


'''
Create the data base with all CSV files
'''
def create_DataBase():
   for fileName in range(0, 11):      
      dataBase(fileName)
      pass 
   pass


'''
Create a list with all data from the data base
@return dataList the list with database values
'''
def dataBase_to_List():
   connect = sqlite3.connect("lp_master.db")
   c = connect.cursor()   
   c.execute('''select * from teachers''')
  
   for x in c.fetchall():
      dataList.append(x)
      pass
   return dataList




'''
Create stistics 1 - number os establishments per year
@return myList the list with the statistics values
'''
def stats_1():   
   dataList = dataBase_to_List()
    
   dataList = sorted(dataList, key=itemgetter(6,8)) 
   count = 0
   myList = []
   tempVars =[]
   tempVars.insert(0,(dataList[0][8],dataList[0][6]))   
   for x in dataList:      

      if x != dataList[len(dataList)-1]:#different from last value at dataList 
         if  x[8] != tempVars[0][0]:    #different stablishment names 
            if x[6] == tempVars[0][1]:  #equal year 
               count += 1
            else:
               myList.append((count, tempVars[0][1]))  
               count = 1
               pass
            pass
      else:
         myList.append((count, tempVars[0][1]))  
         count = 1
         pass

      
      tempVars.insert(0,(x[8],x[6]))        
      pass
   return myList

'''
write a CSV file with stistic 1
@param oneList the list with the statistic 1 values
'''
def stats1_toCSV(oneList):
 
   f = open('stats1.csv', 'wb')
   writer = csv.writer(f)
   writer.writerow(('Numero de Estabelecimentos', 'Ano')) 
   
   for x in oneList:
      writer.writerow((str(x[0]), str(x[1])))     

   f.close()
   

def stats1_toHTML(oneList):
   f = open('./stats1.htm', 'w')
   f.write('<html>')
   f.write('<body>')
   
   f.write('<h1>Estabelecimentos por ano:</h1>')

   f.write('<table border="1">')
   f.write('<tr>')
   f.write('<th>Numero de Estabelecimentos</th>')
   f.write('<th>Ano</th>')
   f.write('</tr>')
   
   for x in oneList:
      f.write('<tr>')
      f.write('<td>' + str(x[0]) + '</td>')
      f.write('<td>'+ str(x[1]) + '</td>')
      f.write('</tr>')
       


   f.write('</table>')
   f.write('</body>')
   f.write('</html>')
   f.close()
   


'''
Create stistics 2-  teachers per category per stablishment per year
@return myList the list with the statistics values
'''
def stats_2():
   
   dataList = dataBase_to_List()
    
   dataList = sorted(dataList, key=itemgetter(8,6,4)) 
   count = 0
   myList = []
   tempVars =[]
   tempVars.insert(0,(dataList[0][8],dataList[0][6], dataList[0][4]))  
   for x in dataList:
      #stablishment name is different
      #year is different
      #category is different
      if x[8] != tempVars[0][0] or x[6] != tempVars[0][1] or x[4] != tempVars[0][2]:  
         myList.append((count, tempVars[0][2], tempVars[0][0],tempVars[0][1]))
         count = 1
      else:
         count += 1
         
         pass
      tempVars.insert(0,(x[8],x[6],x[4]))        
      pass
   return myList

'''
write a CSV file with data from stistic 2
@param oneList the list with statistic values
'''
def stats2_toCSV(oneList):
 
   f = open('stats2.csv', 'wb')
   writer = csv.writer(f)
   writer.writerow(('Numero de Professores', 'Categoria', 'Nome Estabelecimento', 'Ano')) 
   
   for x in oneList:
      writer.writerow((str(x[0]), (x[1]).encode('utf-8'),(x[2]).encode('utf-8'), (x[3]).encode('utf-8')))     

   f.close()


'''
write a HTML file with data from stistic 2
@param oneList the list with statistic values
'''  
def stats2_toHTML(oneList):
   f = open('./stats2.htm', 'w')
   f.write('<html>')
   f.write('<body>')
   f.write('<h1>Professores por categoria por estabelecimento por ano:</h1>')
   f.write('<table border="1">')
   f.write('<tr>')
   f.write('<th>Numero de Professores</th>')
   f.write('<th>Categoria</th>')
   f.write('<th>Nome Estabelecimento</th>')
   f.write('<th>Ano</th>')
   f.write('</tr>')
   
   for x in oneList:
      f.write('<tr>')
      f.write('<td>'+ str(x[0]) + '</td>')
      f.write('<td>'+ (x[1]).encode('utf-8') + '</td>')
      f.write('<td>'+ (x[2]).encode('utf-8') + '</td>')
      f.write('<td>'+ (x[3]).encode('utf-8') + '</td>')
      f.write('</tr>') 
      
   f.write('</table>')
   f.write('</body>')
   f.write('</html>')
   f.close()




'''
Create stistics 3-  teachers per category per stablishment per year
@return myList the list with the statistics values
'''
def stats_3():
   
   dataList = dataBase_to_List()
    
   dataList = sorted(dataList, key=itemgetter(8,6,4,2)) 
   count = 0
   myList = []
   tempVars =[]
   tempVars.insert(0,(dataList[0][2], dataList[0][8],dataList[0][4], dataList[0][6]))  
   for x in dataList:
     
      if x[2] != tempVars[0][0] or x[8] != tempVars[0][1] or x[4] != tempVars[0][2] or x[6] != tempVars[0][3]:  
         myList.append((count, tempVars[0][0], tempVars[0][1],tempVars[0][2], tempVars[0][3]))
         count = 1
      else:
         count += 1
         
         pass
      tempVars.insert(0,(x[2], x[8], x[4], x[6]))        
      pass
   return myList

'''
write a CSV file with data from stistic 3
@param oneList the list with statistic values
'''
def stats3_toCSV(oneList):
 
   f = open('stats3.csv', 'wb')
   writer = csv.writer(f)
   writer.writerow(('Numero de Professores', 'Grau','Nome Estabelecimento', 'Categoria', 'Ano')) 
   
   for x in oneList:
      writer.writerow((str(x[0]), (x[1]).encode('utf-8'), (x[2]).encode('utf-8'), (x[3]).encode('utf-8'), str(x[4])))     

   f.close()
   
'''
write a HTML file with data from stistic 3
@param oneList the list with statistic values
'''
def stats3_toHTML(oneList):
   f = open('./stats3.htm', 'w')
   f.write('<html>')
   f.write('<body>')
   f.write('<h1>Professores por grau:</h1>')
   f.write('<table border="1">')
   f.write('<tr>')
   f.write('<th>Numero de Professores</th>')
   f.write('<th>Grau</th>')
   f.write('<th>Nome Estabelecimento</th>')
   f.write('<th>Categoria</th>')   
   f.write('<th>Ano</th>')
   f.write('</tr>')
   
   for x in oneList:
      f.write('<tr>')
      f.write('<td>'+ str(x[0]) + '</td>')
      f.write('<td>'+ (x[1]).encode('utf-8') + '</td>')
      f.write('<td>'+ (x[2]).encode('utf-8') + '</td>')
      f.write('<td>'+ (x[3]).encode('utf-8') + '</td>')
      f.write('<td>'+ (x[4]).encode('utf-8') + '</td>')
      f.write('</tr>') 
      
   f.write('</table>')   
   f.write('</body>')
   f.write('</html>')
   f.close()
  


'''
Create local HTTP Server
'''
def http_simpleServer():
   PORT = 8000
   Handler = SimpleHTTPServer.SimpleHTTPRequestHandler   
   httpd = SocketServer.TCPServer(("", PORT), Handler)   
   print "serving at port", PORT
   webbrowser.open_new("http://127.0.0.1:8000") 
   httpd.serve_forever()
  
   
   
   



    

